Mega Code Archive
Aggregate Functions
1) Add an OTHER and TOTAL column to the report
2) Aggregate function and alias column name
3) Aggregate function and group by clause
4) All rows with the max value
5) Average Price Per Department
6) Average Price Per Departments Having More Than 3 Products
7) Average salary in Toronto
8) AVG
9) AVG function and NULLs
10) AVG(DISTINCT salary)
11) Column alias name for count function
12) Combine the max and avg function together
13) Compute sum on salary
14) Count date field value, and calculation
15) Count department and calculate average salary
16) Count distinct column value
17) Count employee in a department after grouping by department id
18) Count employee, group by department id and job title
19) Count null
20) Count with group by
21) Count() - count(column_Name)
22) Count() - count(onhand)
23) Count() and column renaming
24) Count() vs count(column name)
25) Count() with where clause
26) Count(1)
27) COUNT(column) and COUNT()
28) Count(distinct state)
29) DISTINCT operator inside a group function
30) Display the order number and total order price of the order(s) with the maximum total order price
31) Doing calculation in sum() function
32) Employees whose salary is more than average salary
33) Employees whose salary is more than average salary(use having clause only)
34) Example using the COUNT function with group by clause
35) Example using the MIN function with group by clause
36) Example using the MIN function with having clause
37) Find out which maker has a null value car with count()
38) Find the average of the uniquely priced cars
39) Get the number of rows in each city group using the COUNT() function
40) GROUP BY clause and AVG() function
41) GROUP BY function would produce inventory totals distributed across different vendors
42) Having with aggregate function
43) Having with avg
44) In Oracle, nulls in calculations involving aggregate functions are ignored
45) Limit the rows passed to the aggregate function using a WHERE clause
46) MAX
47) Max - Min
48) MAX with character data, retrieves the highest value alphabetically (that is, closest z)
49) Max with null value
50) Max(total_price) - min(total_price)
51) MIN
52) MIN with character data, retrieves the lowest value alphabetically (that is, closest to a)
53) MIN with dates, it gives you the oldest dates
54) Min with null value
55) Order by the result of aggregate function
56) Retrieves the city and average salary of the employees grouped by city
57) SUM
58) Sum column for a certain time period
59) SUM function and NULLs
60) Sum salary group by department number
61) Sum salary over
62) Sum with column calculation
63) Sum() function and having clause
64) Syntax
65) To find out the average price for each manufacturer
66) To find the price range for the cars on the lot in relation to the manufacturer, the GROUP BY clause must be used
67) Use avg and nvl together
68) Use MAX with dates, it gives you the most recent dates
69) Use NVL in set statement
70) Use the aggregate functions with any valid expression
71) Using GROUP BY would tell us how many cars from each vendor we have on the lot
72) -Using the COUNT function in Having clause
73) Using the SUM function with GROUP BY Clause
74) Using the WHERE clause with group functions
75) Who have the max value
76) Wrap case when into sum() function